from django.http import JsonResponse, HttpResponse
from openpyxl.workbook import Workbook
from rest_framework.views import APIView
from django.contrib.auth.hashers import make_password, check_password
import pandas as pd
from django.db import connection
from datetime import datetime
from .models import Student, SysStuSerializer, SysTeaSerializer, Teacher, MutualSelection, MutualSelectionSerializer
from user.models import SysUser
from role.models import SysUserRole
from django.views import View
import json
from django.core.paginator import Paginator
from django.db.models import Q
import io


# Create your views here.

# 上传学生信息
class ExcelStuUploadView(APIView):
    def post(self, request):
        if 'excel_file' not in request.FILES:
            return JsonResponse({'error': '未提供 Excel 文件'}, status=400)

        excel_file = request.FILES['excel_file']
        try:
            df = pd.read_excel(excel_file)
            # 这里可以添加解析数据并保存到数据库的逻辑
            # 例如，假设保存到 Student 模型
            for index, row in df.iterrows():
                # 创建学生
                if row['姓名'] != '***':
                    # 创建系统用户
                    newUser = SysUser.objects.create(
                        username=row['身份证号'],
                        password=make_password('123456'),
                        phonenumber=row['联系方式'],
                        avatar="default.jpg",
                        create_time=datetime.now().date(),
                        status=1,
                        # 其他字段以此类推
                    )

                    # 创建学生表
                    create_stu = Student.objects.create(
                        stu_id=row['身份证号'],
                        stu_name=row['姓名'],
                        stu_phone=row['联系方式'],
                        stu_nation=row['民族'],
                        stu_politic_outlook=row['政治面貌'],
                        stu_birth=row['出生年月'],
                        stu_enrollment_year=row['入学年份'],
                        stu_type=row['类型（专硕/学硕）'],
                        stu_gender=row['性别'],
                        stu_major=row['研究生专业'],
                        stu_graduate_school=row['本科毕业院校'],
                        stu_graduate_major=row['本科毕业专业'],
                        stu_fresh_previous=row['应/历届'],
                        stu_grade_english=row['英语成绩'],
                        stu_grade_math=row['数学成绩'],
                        stu_grade_politics=row['政治成绩'],
                        stu_grade_procourse=row['专业课成绩'],
                        stu_grade_total=row['总分'],
                        stu_hobby=row['爱好特长'],
                        stu_honor=row['获得荣誉'],
                        stu_workexperience=row['工作经历'],
                        user_id=newUser.id,
                        # 其他字段以此类推
                    )

                    # 创建角色
                    role = SysUserRole.objects.create(
                        role_id=23,
                        user_id=newUser.id,
                    )

            return JsonResponse({'message': '文件处理成功'}, status=200)
        except Exception as e:
            return JsonResponse({'error': str(e)}, status=400)


# 上传导师信息
class ExcelTeaUploadView(APIView):
    def post(self, request):
        if 'excel_file' not in request.FILES:
            return JsonResponse({'error': '未提供 Excel 文件'}, status=400)

        excel_file = request.FILES['excel_file']
        try:
            df = pd.read_excel(excel_file)
            # 这里可以添加解析数据并保存到数据库的逻辑
            # 例如，假设保存到 Student 模型
            for index, row in df.iterrows():
                # 创建学生
                if row['姓名'] != '***':
                    # 创建系统用户
                    newUser = SysUser.objects.create(
                        username=row['工号'],
                        password=make_password('123456'),
                        phonenumber=row['联系方式'],
                        avatar="default.jpg",
                        create_time=datetime.now().date(),
                        status=1,
                        # 其他字段以此类推
                    )

                    # 创建老师表
                    teacher_quota_M = row['专硕名额']
                    if pd.isna(teacher_quota_M):
                        teacher_quota_M = 0

                    # Handle NaN values for teacher_quota_P
                    teacher_quota_P = row['学硕名额']
                    if pd.isna(teacher_quota_P):
                        teacher_quota_P = 0
                    create_tea = Teacher.objects.create(
                        teacher_id=row['工号'],
                        teacher_name=row['姓名'],
                        teacher_field=row['招生领域'],
                        teacher_gender=row['性别'],
                        teacher_introduction=row['导师简介'],
                        teacher_title=row['职称'],
                        teacher_direction=row['研究方向'],
                        teacher_education=row['学历'],
                        teacher_phone=row['联系方式'],
                        teacher_dorm=row['类型（博导、硕导（学硕、专硕））'],
                        teacher_quota_M=teacher_quota_M,
                        teacher_quota_P=teacher_quota_P,
                        teacher_project=row['研究项目'],
                        teacher_honor=row['获得荣誉'],
                        user_id=newUser.id,
                        # 其他字段以此类推
                    )
                    # 创建角色
                    role = SysUserRole.objects.create(
                        role_id=24,
                        user_id=newUser.id,
                    )

            return JsonResponse({'message': '文件处理成功'}, status=200)
        except Exception as e:
            return JsonResponse({'error': str(e)}, status=400)


class SearchStuView(View):
    def post(self, request):
        data = json.loads(request.body.decode("utf-8"))
        pageNum = data['pageNum']  # 当前页
        pageSize = data['pageSize']  # 每页大小
        query = data['query']  # 查询参数
        stuListPage = Paginator(Student.objects.filter(stu_name__icontains=query), pageSize).page(pageNum)
        obj_stus = stuListPage.object_list.values()  # 转成字典
        stus = list(obj_stus)  # 把外层的容器转为List
        # for stu in stus:
        #     userId = user['id']
        #     roleList = SysRole.objects.raw(
        #         "SELECT id,NAME FROM sys_role WHERE id IN (SELECT role_id FROM sys_user_role WHERE user_id=" + str(
        #             userId) + ")")
        #     roleListDict = []
        #     for role in roleList:
        #         roleDict = {}
        #         roleDict['id'] = role.id
        #         roleDict['name'] = role.name
        #         roleListDict.append(roleDict)
        #         user['roleList'] = roleListDict
        total = Student.objects.filter(stu_name__icontains=query).count()
        return JsonResponse({'code': 200, 'stuList': stus, 'total': total})


class ActionStuView(View):
    def get(self, request):
        """
        根据id获取用户信息
        :param request:
        :return:
        """
        stu_id = request.GET.get("stu_id")
        stu_object = Student.objects.get(stu_id=stu_id)
        return JsonResponse({'code': 200, 'stu': SysStuSerializer(stu_object).data})

    # def delete(self, request):
    #     """
    #     删除操作 三张表， 用户表， 学生表，双选结果表
    #     :param request:
    #     :return:
    #     """
    #
    #     idList = json.loads(request.body.decode("utf-8"))
    #     SysUserRole.objects.filter(user_id__in=idList).delete()
    #     SysUser.objects.filter(id__in=idList).delete()
    #     return JsonResponse({'code': 200})


class SearchTeaView(View):
    def post(self, request):
        data = json.loads(request.body.decode("utf-8"))
        pageNum = data['pageNum']  # 当前页
        pageSize = data['pageSize']  # 每页大小
        query = data['query']  # 查询参数
        status = data['status']  # 查询参数
        username = data['username']  # 查询username

        flag = False
        if (username != ''):
            user = SysUser.objects.get(username=username)
            # 查询角色
            roles = SysUserRole.objects.filter(user_id=user.id).values()
            roles = list(roles)
            flag = False
            for role in roles:
                if role['role_id'] == 24:
                    flag = True
        if flag:
            teaListPage = Teacher.objects.filter(teacher_name__icontains=query, teacher_id=username).values()
        else:
            teaListPage = Teacher.objects.filter(teacher_name__icontains=query).values()
        # obj_teas = teaListPage.object_list.values()  # 转成字典
        teas = list(teaListPage)  # 把外层的容器转为List
        for tea in teas:
            teacher_id = tea['teacher_id']
            # 导师有的学硕名额
            teacher_quota_M = tea['teacher_quota_M']
            teacher_quota_P = tea['teacher_quota_P']
            # 专硕名额
            zhuan_shuo = MutualSelection.objects.filter(teacher_id=teacher_id, type='专硕').values()
            xue_shuo = MutualSelection.objects.filter(teacher_id=teacher_id, type='学硕').values()

            zhuan_shuo = int(teacher_quota_M) - len(zhuan_shuo)
            xue_shuo = int(teacher_quota_P) - len(xue_shuo)

            tea['teacher_quota_M'] = zhuan_shuo if zhuan_shuo > 0 else 0
            tea['teacher_quota_P'] = xue_shuo if xue_shuo > 0 else 0
        total = Teacher.objects.filter(teacher_name__icontains=query).count()
        if status == "":
            # 计算切片范围
            start = (pageNum - 1) * pageSize
            end = start + pageSize
            teas = teas[start:end]
        elif status == '1':
            # 配额已满
            new_teas = []
            for tea in teas:
                if tea['teacher_quota_M'] == 0 and tea['teacher_quota_P'] == 0:
                    new_teas.append(tea)
            # 计算切片范围
            start = (pageNum - 1) * pageSize
            end = start + pageSize
            teas = new_teas[start:end]
            total = len(new_teas)
        elif status == '2':
            # 配额未满
            new_teas = []
            for tea in teas:
                if tea['teacher_quota_M'] > 0 or tea['teacher_quota_P'] > 0:
                    new_teas.append(tea)
            # 计算切片范围
            start = (pageNum - 1) * pageSize
            end = start + pageSize
            teas = new_teas[start:end]
            total = len(new_teas)
        return JsonResponse({'code': 200, 'teaList': teas, 'total': total})


class SaveStuView(View):

    def post(self, request):
        data = json.loads(request.body.decode("utf-8"))
        if data['stu_id'] == '-1':  # 添加
            obj_stu = Student(
                stu_id=data['stu_id'],
                stu_name=data['stu_name'],
                stu_phone=data['stu_phone'],
                stu_nation=data['stu_nation'],
                stu_politic_outlook=data['stu_politic_outlook'],
                stu_birth=data['stu_birth'],
                stu_enrollment_year=data['stu_enrollment_year'],
                stu_type=data['stu_type'],
                stu_gender=data['stu_gender'],
                stu_major=data['stu_major'],
                stu_graduate_school=data['stu_graduate_school'],
                stu_graduate_major=data['stu_graduate_major'],
                stu_fresh_previous=data['stu_fresh_previous'],
                stu_grade_english=data['stu_grade_english'],
                stu_grade_math=data['stu_grade_math'],
                stu_grade_politics=data['stu_grade_politics'],
                stu_grade_procourse=data['stu_grade_procourse'],
                stu_grade_total=data['stu_grade_total'],
                stu_hobby=data['stu_hobby'],
                stu_honor=data['stu_honor'],
                stu_workexperience=data['stu_workexperience'],
            )
            obj_stu.create_time = datetime.now().date()
            obj_stu.save()
        else:  # 修改
            obj_stu = Student(
                stu_id=data['stu_id'],
                stu_name=data['stu_name'],
                stu_phone=data['stu_phone'],
                stu_nation=data['stu_nation'],
                stu_politic_outlook=data['stu_politic_outlook'],
                stu_birth=data['stu_birth'],
                stu_enrollment_year=data['stu_enrollment_year'],
                stu_type=data['stu_type'],
                stu_gender=data['stu_gender'],
                stu_major=data['stu_major'],
                stu_graduate_school=data['stu_graduate_school'],
                stu_graduate_major=data['stu_graduate_major'],
                stu_fresh_previous=data['stu_fresh_previous'],
                stu_grade_english=data['stu_grade_english'],
                stu_grade_math=data['stu_grade_math'],
                stu_grade_politics=data['stu_grade_politics'],
                stu_grade_procourse=data['stu_grade_procourse'],
                stu_grade_total=data['stu_grade_total'],
                stu_hobby=data['stu_hobby'],
                stu_honor=data['stu_honor'],
                stu_workexperience=data['stu_workexperience'],
            )
            obj_stu.update_time = datetime.now().date()
            obj_stu.save()
        return JsonResponse({'code': 200})


class SaveTeaView(View):

    def post(self, request):
        data = json.loads(request.body.decode("utf-8"))
        if data['teacher_id'] == -1:  # 添加
            obj_tea = Teacher(
                teacher_id=data['teacher_id'],
                teacher_name=data['teacher_name'],
                teacher_gender=data['teacher_gender'],
                teacher_introduction=data['teacher_introduction'],
                teacher_title=data['teacher_title'],
                teacher_direction=data['teacher_direction'],
                teacher_education=data['teacher_education'],
                teacher_phone=data['teacher_phone'],
                teacher_dorm=data['teacher_dorm'],
                teacher_quota_M=data['teacher_quota_M'],
                teacher_quota_P=data['teacher_quota_P'],
                teacher_field=data['teacher_field'],
                teacher_project=data['teacher_project'],
                teacher_honor=data['teacher_honor'],
            )
            obj_tea.create_time = datetime.now().date()
            obj_tea.save()
        else:  # 修改
            obj_tea = Teacher(
                teacher_id=data['teacher_id'],
                teacher_name=data['teacher_name'],
                teacher_gender=data['teacher_gender'],
                teacher_introduction=data['teacher_introduction'],
                teacher_title=data['teacher_title'],
                teacher_direction=data['teacher_direction'],
                teacher_education=data['teacher_education'],
                teacher_phone=data['teacher_phone'],
                teacher_dorm=data['teacher_dorm'],
                teacher_quota_M=data['teacher_quota_M'],
                teacher_quota_P=data['teacher_quota_P'],
                teacher_field=data['teacher_field'],
                teacher_project=data['teacher_project'],
                teacher_honor=data['teacher_honor'],
            )
            obj_tea.update_time = datetime.now().date()
            obj_tea.save()
        return JsonResponse({'code': 200})


class ActionTeaView(View):
    def get(self, request):
        """
        根据id获取用户信息
        :param request:
        :return:
        """
        teacher_id = request.GET.get("teacher_id")
        tea_object = Teacher.objects.get(teacher_id=teacher_id)
        return JsonResponse({'code': 200, 'tea': SysTeaSerializer(tea_object).data})

    # def delete(self, request):
    #     """
    #     删除操作 三张表， 用户表， 学生表，双选结果表
    #     :param request:
    #     :return:
    #     """
    #
    #     idList = json.loads(request.body.decode("utf-8"))
    #     SysUserRole.objects.filter(user_id__in=idList).delete()
    #     SysUser.objects.filter(id__in=idList).delete()
    #     return JsonResponse({'code': 200})


# 学生选择导师
class saveSelectTeaView(View):
    def post(self, request):
        data = json.loads(request.body.decode("utf-8"))

        # 判断是否存在重复
        is_exist = MutualSelection.objects.filter(stu_id=data['stu_id']).values()
        if len(is_exist) > 0:
            return JsonResponse({'code': 202, 'message': "您已存在选择结果，请退选后重新选择!"})

        # 查询该学生的信息
        stu = Student.objects.get(stu_id=data['stu_id'])

        # 判断导师是否超出名额
        has_select = MutualSelection.objects.filter(teacher_id=data['teacher_id'], type=stu.stu_type).values()

        # 查询该导师-该类型的名额
        quota = Teacher.objects.get(teacher_id=data['teacher_id'])
        if stu.stu_type == '学硕':
            if int(quota.teacher_quota_P) <= len(has_select):
                return JsonResponse({'code': 202, 'message': "该导师的" + stu.stu_type + "名额已经报满!"})

        if stu.stu_type == '专硕':
            if int(quota.teacher_quota_M) <= len(has_select):
                return JsonResponse({'code': 202, 'message': "该导师的" + stu.stu_type + "名额已经报满!"})

        # 如果不满 则进行插入操作
        obj_tea = MutualSelection(
            teacher_id=data['teacher_id'],
            teacher_name=data['teacher_name'],
            stu_id=data['stu_id'],
            stu_name=stu.stu_name,
            directions=data['directions'],
            type=stu.stu_type,
        )
        obj_tea.create_time = datetime.now().date()
        obj_tea.save()

        return JsonResponse({'code': 200})


# 学生退选导师
class delSelectTeaView(View):
    def post(self, request):
        data = json.loads(request.body.decode("utf-8"))

        # 判断是否存在
        is_exist = MutualSelection.objects.filter(stu_id=data['stu_id']).values()
        if len(is_exist) < 1:
            return JsonResponse({'code': 202, 'message': "您暂时还未选择导师！"})

        # 存在结果 - 直接删除
        has_select = MutualSelection.objects.get(stu_id=data['stu_id'], teacher_id=data['teacher_id'])
        has_select.delete()
        return JsonResponse({'code': 200})


class delSelectStuView(View):
    def post(self, request):
        data = json.loads(request.body.decode("utf-8"))

        # 判断是否存在
        is_exist = MutualSelection.objects.filter(teacher_id=data['teacher_id'], stu_id=data['stu_id']).values()
        if len(is_exist) < 1:
            return JsonResponse({'code': 202, 'message': "不存在选择结果！"})

        # 存在结果 - 直接删除
        has_select = MutualSelection.objects.get(teacher_id=data['teacher_id'], stu_id=data['stu_id'])
        has_select.delete()
        return JsonResponse({'code': 200})


# 选择结果列表
class SearchSelectResView(View):
    def post(self, request):
        data = json.loads(request.body.decode("utf-8"))
        pageNum = data['pageNum']  # 当前页
        pageSize = data['pageSize']  # 每页大小
        query = data['query']  # 查询参数
        status = data['status']
        if status == '':
            # 查询全部

            sql_query_str = "SELECT a.stu_id,a.stu_name ,b.* from stu_teacher_student as a  LEFT JOIN stu_teacher_mutualselection as b on a.stu_id = b.stu_id  where (a.stu_name like  '%" + query + "%'  or b.teacher_name like '%" + query + "% ')"
            sql_query_str_count = "SELECT COUNT(*) from stu_teacher_student as a  LEFT JOIN stu_teacher_mutualselection as b on a.stu_id = b.stu_id  where (a.stu_name like  '%" + query + "%'  or b.teacher_name like '%" + query + "% ')"
            select_res = Paginator(Student.objects.raw(sql_query_str),
                                   pageSize).page(pageNum)
            select_res_list = []
            for item in select_res.object_list:
                select_res_list.append({
                    'stu_id': item.stu_id,
                    'stu_name': item.stu_name,
                    'teacher_name': item.teacher_name,  # Include other fields from your SQL query
                    'type': item.type,  # Include other fields from your SQL query
                    'directions': item.directions,  # Include other fields from your SQL query
                    # Add other Student fields as needed (e.g., stu_phone, stu_type)
                })
            with connection.cursor() as cursor:
                cursor.execute(f"{sql_query_str_count}")
                total = cursor.fetchone()[0]

        elif status == '1':
            # 未选择
            sql_query_str = "SELECT a.stu_id,a.stu_name  ,b.* from stu_teacher_student as a  LEFT JOIN stu_teacher_mutualselection as b on a.stu_id = b.stu_id  where (a.stu_name like  '%" + query + "%'  or b.teacher_name like '%" + query + "%')  and  ISNULL(b.teacher_name)"
            sql_query_str_count = "SELECT COUNT(*) from stu_teacher_student as a  LEFT JOIN stu_teacher_mutualselection as b on a.stu_id = b.stu_id  where (a.stu_name like  '%" + query + "%'  or b.teacher_name like '%" + query + "%')  and  ISNULL(b.teacher_name)"
            select_res = Paginator(Student.objects.raw(sql_query_str),
                                   pageSize).page(pageNum)
            select_res_list = []
            for item in select_res.object_list:
                select_res_list.append({
                    'stu_id': item.stu_id,
                    'stu_name': item.stu_name,
                    'teacher_name': item.teacher_name,  # Include other fields from your SQL query
                    'type': item.type,  # Include other fields from your SQL query
                    'directions': item.directions,  # Include other fields from your SQL query
                    # Add other Student fields as needed (e.g., stu_phone, stu_type)
                })
            with connection.cursor() as cursor:
                cursor.execute(sql_query_str_count)
                total = cursor.fetchone()[0]
        elif status == '2':
            # 查询已选择
            sql_query_str = "SELECT a.stu_id,a.stu_name ,b.* from stu_teacher_student as a  LEFT JOIN stu_teacher_mutualselection as b on a.stu_id = b.stu_id  where (a.stu_name like  '%" + query + "%'  or b.teacher_name like '%" + query + "%')  and  not ISNULL(b.teacher_name)"
            sql_query_str_count = "SELECT COUNT(*) from stu_teacher_student as a  LEFT JOIN stu_teacher_mutualselection as b on a.stu_id = b.stu_id  where (a.stu_name like  '%" + query + "%'  or b.teacher_name like '%" + query + "%')  and  not ISNULL(b.teacher_name)"
            select_res = Paginator(Student.objects.raw(sql_query_str),
                                   pageSize).page(pageNum)
            select_res_list = []
            for item in select_res.object_list:
                select_res_list.append({
                    'stu_id': item.stu_id,
                    'stu_name': item.stu_name,
                    'teacher_name': item.teacher_name,  # Include other fields from your SQL query
                    'type': item.type,  # Include other fields from your SQL query
                    'directions': item.directions,  # Include other fields from your SQL query
                    # Add other Student fields as needed (e.g., stu_phone, stu_type)
                })
            with connection.cursor() as cursor:
                cursor.execute(sql_query_str_count)
                total = cursor.fetchone()[0]
        return JsonResponse({'code': 200, 'selectResList': select_res_list, 'total': total})

    def get(self, request):
        teacher_id = request.GET.get("teacher_id")
        if len(teacher_id) < 2:
            return JsonResponse({'code': 202, 'message': "教师工号错误！"})
        obj_select_res = MutualSelection.objects.filter(teancher_id=teacher_id).values()
        select_res = list(obj_select_res)  # 把外层的容器转为List
        return JsonResponse({'code': 200, 'selectResList': select_res})


# 获取学生选择的导师
class SearchSelectStuView(View):
    def get(self, request):
        """
        根据id获取用户信息
        :param request:
        :return:
        """
        teacher_id = request.GET.get("teacher_id")
        if len(teacher_id) < 2:
            return JsonResponse({'code': 202, 'message': "教师工号错误！"})
        try:

            teacher_id = request.GET.get("teacher_id")

            stus_object = MutualSelection.objects.raw(
                "SELECT a.*,b.* from stu_teacher_mutualselection as a LEFT JOIN stu_teacher_student as b on  a.stu_id=b.stu_id where a.teacher_id = " + teacher_id)
            # 将查询结果转换为字典列表
            result_list = []
            for obj in stus_object:
                obj_dict = {}
                for field in Student._meta.get_fields():
                    if field.concrete:  # 仅处理具体字段
                        try:
                            obj_dict[field.name] = getattr(obj, field.name)
                        except Exception:
                            continue
                result_list.append(obj_dict)

        except MutualSelection.DoesNotExist:
            return JsonResponse({'code': 201, 'message': "数据不存在！"})

        return JsonResponse({'code': 200, 'select_stus': result_list})


class SearchSelectTeaItemView(View):
    def get(self, request):
        """
        根据id获取用户信息
        :param request:
        :return:
        """
        stu_id = request.GET.get("stu_id")
        try:
            tea_object = MutualSelection.objects.get(stu_id=stu_id)
        except MutualSelection.DoesNotExist:
            return JsonResponse({'code': 201, 'message': "数据不存在！"})

        return JsonResponse({'code': 200, 'select_tea': MutualSelectionSerializer(tea_object).data})


class ExportSelectAllView(View):
    def get(self, request):
        """
        根据id获取用户信息
        :param request:
        :return:
        """

        try:
            tea_object = MutualSelection.objects.all().values()
        except MutualSelection.DoesNotExist:
            return JsonResponse({'code': 201, 'message': "数据不存在！"})

        return JsonResponse({'code': 200, 'select_tea': MutualSelectionSerializer(tea_object).data})

# class ExportSelectAll(View):
#
#     def get(self, request):
#         try:
#             # 从数据库查询数据
#             students = MutualSelection.objects.all().values()
#             data = list(students)
#
#             df = pd.DataFrame(data)
#
#             # 创建一个内存中的 Excel 文件
#             output = io.BytesIO()
#             with pd.ExcelWriter(output, engine='openpyxl') as writer:
#                 df.to_excel(writer, sheet_name='Sheet1', index=False)
#
#             output.seek(0)
#
#             # 创建响应对象
#             response = HttpResponse(output,
#                                     content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
#             response['Content-Disposition'] = 'attachment; filename=result.xlsx'
#             return response
#         except MutualSelection.DoesNotExist:
#             return JsonResponse({'code': 201, 'message': "数据不存在！"})
